﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using University.Workshop.Model;
using System.Collections.ObjectModel;

namespace University.Workshop.DataAccess
{
    public static class UserDataAccess
    {
        public const string ConnString = "Data Source=.\\SQLEXPRESS;Initial Catalog=MedicalDatabase;Integrated Security = True";

        static MedicalCenterEntities entities = MedicalCenterEntities.Instance;

 public static User InsertUser(User user)
        {
            if (user != null)
            {

                using (SqlConnection conn = new SqlConnection(ConnString))
                {

                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "INSERT INTO [User](UserId,FirstName,LastName,LandPhone,CellPhone,Address,Role,LogOn,Password) VALUES(@Ci,@Nombre,@ApellidoPaterno,@Telefono,@Celular,@Direccion,@Tipo,@Login,@Password)";
                    cmd.Parameters.AddWithValue("Ci", user.UserId);
                    cmd.Parameters.AddWithValue("Nombre", user.FirstName);
                    cmd.Parameters.AddWithValue("ApellidoPaterno", user.LastName);
                    cmd.Parameters.AddWithValue("Telefono", user.LandPhone);
                    cmd.Parameters.AddWithValue("Celular", user.CellPhone);
                    cmd.Parameters.AddWithValue("Direccion", user.Address);
                    cmd.Parameters.AddWithValue("Tipo", user.Role);
                    cmd.Parameters.AddWithValue("Login", user.LogOn);
                    cmd.Parameters.AddWithValue("Password", user.Password);

                    SqlCommand cmdId = conn.CreateCommand();
                    cmdId.CommandText = "SELECT @@Identity";
                    try
                    {
                        conn.Open();
                        int result = cmd.ExecuteNonQuery();
                        if (result <= 0)
                        {
                            throw new DataAccessException("Error inserting a user in the database");
                        }
                        user.UserId = cmdId.ExecuteScalar().ToString();
                        return user;
                    }
                    catch (SqlException ex)
                    {
                        throw new DataAccessException("Error inserting a user in the database SQL Exception" + ex);
                    }
                    /*finally
                    {
                        if (conn != null && conn.State == System.Data.ConnectionState.Open)
                        {
                            //conn.Close();
                            conn.Dispose();
                        }
                    }*/
                }
            }
            else

                /*entities.Users.Add(user);
                entities.SaveChanges();
                return user;
            }*/
                return null;
        }

        public static User GetUserByLogOn(string logOn)
        {
            if (!string.IsNullOrEmpty(logOn))
            {
                using (SqlConnection conn = new SqlConnection(ConnString))
                {
                    SqlCommand cmd = conn.CreateCommand();
                    cmd.CommandText = "SELECT * FROM [User] Where LogOn = @Login";
                    cmd.Parameters.AddWithValue("Login", logOn);
                    conn.Open();
                    try
                    {
                    
                        SqlDataReader reader = cmd.ExecuteReader();
                        if (!reader.HasRows)
                        {
                            return null;
                        }
                        User user = new User();
                        while (reader.Read())
                        {
                            user.UserId = reader["UserId"].ToString();
                        }
                        return user;
                    }
                    catch (SqlException ex)
                    {
                        throw new DataAccessException("Error obteniendo el usuario", ex);
                    }
                }
            }
            else return null;
        }

        public static User GetUserById(string id)
        {
            if (!string.IsNullOrEmpty(id))
            {
                return entities.Users.Find(id);
            }
            else return null;
        }

        public static User GetUserByUserName(string userName)
        {
            var users = from user in entities.Users
                        where user.LogOn == userName
                        select user;
            return users.First();
        }

        public static ICollection<User> GetAllUsersIdsByRole(string role)
        {
            var users = from user in entities.Users
                        where user.Role == role
                        select user;

            return users.ToList();
        }
        public static bool SignIn(User logged)
        {
            if (logged != null)
            {
                var userLogged = from user in entities.Users
                              where user.LogOn == logged.LogOn
                              select user;
                if (userLogged.Count() == 0)
                    return false;
            }
            return true;
        }
    }
}
